package com.kcs.fxmargin.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.common.utils.ConnectionUtils;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.OIDGenerator;
import com.kcs.fxmargin.bo.UserProFileBO;
import com.kcs.fxmargin.dao.UsersDao;
import com.kcs.fxmargin.model.MUsers;

public class UsersService {
	UsersDao dao 	= new UsersDao();
	
	public List<UserProFileBO> searchUser(UserProFileBO bo) throws Exception{
		Connection conn 			= null;
		PreparedStatement pstm 		= null;
		ResultSet rs				= null;
		List<UserProFileBO> boList 	= new ArrayList<UserProFileBO>();
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
			boList = dao.searchUser(conn, pstm, rs, bo);
			
		} catch (Exception e){
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
			if(rs   != null ) rs.close();
		}
		
		return boList;
	}
	
	public String insertUser(MUsers users) throws Exception {
				
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
			
			users.setId(OIDGenerator.create());
			
			result = dao.insertUser(conn, pstm, users);
			conn.commit();
		} catch (Exception e){
			result ="e";
			conn.rollback();
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
	
	public String updateUser(MUsers users) throws Exception {
		
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.updateUser(conn, pstm, users);
			conn.commit();
		} catch (Exception e){
			result = "e";
			conn.rollback();
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}

	public String deleteUser(String id) throws Exception {
		
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.deleteUser(conn, pstm, id);
			conn.commit();
		} catch (Exception e){
			result = "e";
			conn.rollback();
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
	
	public String loginUpdateUser(String id, String sessionId) throws Exception {
		
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.loginUpdateUser(conn, pstm, id, sessionId);
			conn.commit();
		} catch (Exception e){
			result = "e";
			conn.rollback();
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
	
	public String getSessionId(String id) throws Exception {
		
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		ResultSet rs			= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.getSessionId(conn, pstm, rs, id);
			conn.commit();
		} catch (Exception e){
			result = "e";
			conn.rollback();
			throw e;
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
			if(rs != null )	  rs.close();
		}
		
		return result;
	}
}
